package com.example.computer_exam_training_sys.mapper;

import com.example.computer_exam_training_sys.entity.UserAchievement;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserAchievementMapper {
    
    @Results(id = "achievementMap", value = {
        @Result(property = "achievementId", column = "achievement_id"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "achievementType", column = "achievement_type"),
        @Result(property = "achievementName", column = "achievement_name"),
        @Result(property = "description", column = "description"),
        @Result(property = "earnDate", column = "earn_date"),
        @Result(property = "createTime", column = "create_time"),
        @Result(property = "userName", column = "username")
    })
    @Select("SELECT a.*, u.username FROM user_achievements a " +
            "LEFT JOIN users u ON a.user_id = u.user_id " +
            "WHERE a.user_id = #{userId}")
    List<UserAchievement> findByUserId(Long userId);
    
    @ResultMap("achievementMap")
    @Select("SELECT a.*, u.username FROM user_achievements a " +
            "LEFT JOIN users u ON a.user_id = u.user_id " +
            "WHERE a.user_id = #{userId} AND a.achievement_type = #{achievementType}")
    List<UserAchievement> findByUserIdAndType(@Param("userId") Long userId, @Param("achievementType") String achievementType);
    
    @Insert("INSERT INTO user_achievements (user_id, achievement_type, achievement_name, description, earn_date, create_time) " +
            "VALUES (#{userId}, #{achievementType}, #{achievementName}, #{description}, #{earnDate}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "achievementId")
    int insert(UserAchievement achievement);

    @Select("SELECT COUNT(*) FROM user_achievements WHERE user_id = #{userId}")
    Integer countByUserId(Long userId);
    
    @Select("SELECT COUNT(*) FROM user_achievements WHERE user_id = #{userId} AND achievement_type = #{achievementType}")
    Integer countByUserIdAndType(@Param("userId") Long userId, @Param("achievementType") String achievementType);
    
    @ResultMap("achievementMap")
    @Select("SELECT a.*, u.username FROM user_achievements a " +
            "LEFT JOIN users u ON a.user_id = u.user_id " +
            "WHERE a.user_id = #{userId} ORDER BY a.earn_date DESC LIMIT #{limit}")
    List<UserAchievement> findLatestByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);
    
    // 从user_practices表获取练习相关数据
    @Select("SELECT COUNT(*) FROM user_practices WHERE user_id = #{userId}")
    Integer countPracticesByUserId(Long userId);
    
    @Select("SELECT SUM(question_count) FROM user_practices WHERE user_id = #{userId}")
    Integer getTotalPracticeQuestionsByUserId(Long userId);
    
    @Select("SELECT SUM(correct_count) FROM user_practices WHERE user_id = #{userId}")
    Integer getTotalPracticeCorrectByUserId(Long userId);
    
    @Select("SELECT COUNT(DISTINCT DATE(start_time)) FROM user_practices WHERE user_id = #{userId}")
    Integer getDistinctPracticeDaysByUserId(Long userId);
    
    // 从user_exams表获取考试相关数据
    @Select("SELECT COUNT(*) FROM user_exams WHERE user_id = #{userId}")
    Integer countExamsByUserId(Long userId);
    
    @Select("SELECT AVG(user_score * 1.0 / total_score * 100) FROM user_exams WHERE user_id = #{userId}")
    Double getAverageExamScoreByUserId(Long userId);
    
    @Select("SELECT COUNT(*) FROM user_exams WHERE user_id = #{userId} AND user_score >= total_score * 0.6")
    Integer countPassedExamsByUserId(Long userId);
    
    // 从user_learning_progress表获取学习进度相关数据
    @Select("SELECT COUNT(*) FROM user_learning_progress WHERE user_id = #{userId} AND status = 'completed'")
    Integer countCompletedCoursesByUserId(Long userId);
    
    @Select("SELECT SUM(total_study_time) FROM user_learning_progress WHERE user_id = #{userId}")
    Integer getTotalStudyTimeByUserId(Long userId);
    
    @Select("SELECT COUNT(DISTINCT DATE(last_study_time)) FROM user_learning_progress WHERE user_id = #{userId}")
    Integer getDistinctStudyDaysByUserId(Long userId);
    
    @Select("SELECT MAX(completion_rate) FROM user_learning_progress WHERE user_id = #{userId}")
    Double getMaxCompletionRateByUserId(Long userId);
    
    // 从user_study_statistics表获取学习统计相关数据
    @Select("SELECT SUM(total_study_time) FROM user_study_statistics WHERE user_id = #{userId}")
    Integer getTotalStudyTimeFromStatsByUserId(Long userId);
    
    @Select("SELECT COUNT(DISTINCT stat_date) FROM user_study_statistics WHERE user_id = #{userId}")
    Integer getDistinctStudyDaysFromStatsByUserId(Long userId);
    
    @Select("SELECT COUNT(DISTINCT stat_date) FROM user_study_statistics " +
            "WHERE user_id = #{userId} AND stat_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)")
    Integer getStudyDaysInLast30DaysByUserId(Long userId);
    
    @Select("SELECT MAX(consecutive_days) FROM (" +
            "SELECT COUNT(*) as consecutive_days FROM (" +
            "SELECT stat_date, " +
            "@rank := IF(@prev = DATE_SUB(stat_date, INTERVAL 1 DAY), @rank, @rank + 1) AS `rank`, " +
            "@prev := stat_date " +
            "FROM user_study_statistics, (SELECT @rank := 0, @prev := NULL) AS vars " +
            "WHERE user_id = #{userId} " +
            "ORDER BY stat_date) AS ranked " +
            "GROUP BY `rank`) AS consecutive")
    Integer getMaxConsecutiveStudyDaysByUserId(Long userId);
} 